/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tesam.gedeon.dao.impl;

import com.tesam.gedeon.dao.ProviderDao;
import com.tesam.gedeon.entity.Provider;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.stereotype.Repository;

/**
 *
 * @author joelpacheco
 */
@Repository
public class ProviderDaoImpl extends SimpleJdbcDaoSupport implements ProviderDao {

    @Autowired
    public ProviderDaoImpl(DataSource dataSource) {
        setDataSource(dataSource);
    }

    @Override
    public int save(Provider provider) {

        return getJdbcTemplate().update("INSERT INTO provider"
                + "("
                + "createdBy,modifiedby,dateCreated,status,deleted,presence,ownerid,code,name"
                + ")VALUES(?,?,NOW(),?,?,?,?,?,?)", provider.getCreatedBy(), 0, 1, 0, 1, 1, provider.getCode().trim(), provider.getName().trim());
    }

    @Override
    public int delete(Provider provider) {
        return getJdbcTemplate().update("UPDATE provider SET modifiedby = ?, datemodified = NOW(),deleted = 1 WHERE id = ?", provider.getModifiedBy(), provider.getId());
    }

    @Override
    public int update(Provider provider) {
        return getJdbcTemplate().update("UPDATE provider SET modifiedby = ?,datemodified = NOW(), code = ?,name = ? WHERE id = ?",
                provider.getModifiedBy(), provider.getCode().trim(), provider.getName(), provider.getId());
    }

    @Override
    public Provider getById(int providerId) {

        try {
            return getSimpleJdbcTemplate().queryForObject("SELECT * FROM provider WHERE id = ? and deleted = 0", new BeanPropertyRowMapper<Provider>(Provider.class), providerId);
        } catch (Exception e) {
            System.out.println("Exception-Provider-getById:" + e.getMessage());
        }
        return null;
    }

    @Override
    public List<Provider> getAll() {
        return getSimpleJdbcTemplate().query("SELECT * FROM provider WHERE deleted = 0", new BeanPropertyRowMapper<Provider>(Provider.class));
    }

    @Override
    public List<Provider> getAllByLimit(int startLimit, int endLimit, String dataToSort, String directionOfOrder) {
        String query = null;
        try {

            query = getLimit("SELECT id,createdby,modifiedby, datecreated,datemodified,status,code,name FROM provider WHERE deleted = 0", startLimit, endLimit, dataToSort, directionOfOrder);

            return getSimpleJdbcTemplate().query(query, new BeanPropertyRowMapper<Provider>(Provider.class));

        } catch (Exception e) {
            System.out.println("getAllByLimit : Exception :" + e.getMessage());
            return null;
        }
    }

    private String getLimit(String query, int startLimit, int endLimit,
            String dataToSort, String directionOfOrder) {

        StringBuilder newQuery = new StringBuilder(query);

        newQuery.append(" ORDER BY ");
        newQuery.append(dataToSort);
        newQuery.append(" ");
        newQuery.append(directionOfOrder);
        newQuery.append(" ");
        newQuery.append("LIMIT ");
        newQuery.append(endLimit);
        newQuery.append(" OFFSET ");
        newQuery.append(startLimit);

        return newQuery.toString();
    }

    @Override
    public List<Provider> searchByName(Provider provider, int satartLimit, int endLimit) {
        String query = null;
        try {
            query = getLimit("SELECT id,createdby,modifiedby,datecreated,datemodified,status,code,name FROM provider WHERE deleted = 0 AND upper(name) LIKE ('%'||?||'%')", satartLimit, endLimit, "name", "ASC");
            return getSimpleJdbcTemplate().query(query, new BeanPropertyRowMapper<Provider>(Provider.class), provider.getName());
        } catch (Exception e) {
            System.out.println("searchByName:Exception:" + e.getMessage());
            return null;
        }
    }

    @Override
    public int getCountSearchByName(Provider provider) {
        return getJdbcTemplate().queryForInt("SELECT count(*) FROM provider WHERE deleted = 0 AND upper(name) LIKE ('%' || ? || '%')", provider.getName());
    }

    @Override
    public int getCountTotal() {
        return getJdbcTemplate().queryForInt("SELECT count(*) FROM provider WHERE deleted = 0");
    }
}
